//
//  MainDataBase.m
//  Conference
//
//  Created by 陈鑫 on 12-11-16.
//  Copyright (c) 2012年 __MyCompanyName__. All rights reserved.
//

#import "MainDataBase.h"
#import "Constant.h"
#import "AlarmData.h"
#import "Member.h"
#import "ScheduleData.h"
#import "AppDelegate.h"
#import "Globals.h"
#import "NewsData.h"
#import "AttachData.h"
#import "EleSpeakData.h"
#import "FlightData.h"
#import "PlaceData.h"
@implementation MainDataBase
@synthesize db;
static MainDataBase *sharedManager=nil ;

+(MainDataBase *)getInstance{
	@synchronized(self){
		if(sharedManager ==nil){
			sharedManager=[[self alloc] init];	
		}
	}
	return sharedManager;
}

-(id)init{
	if (self=[super init]) {
        TAB_CONFERENCESALARM=@"tb_alarm";
        TAB_CONFERENCESSCHEDULE=@"tb_schedule";
        TAB_NEWS=@"tb_news";
        TAB_ATTACHINFO=@"tb_attachinfo";
        TAB_ELESPEAK=@"tb_elespeak";
        TAB_FLIGHT=@"tb_flight";
        TAB_USER=@"tb_user";
        TAB_PLACE=@"tb_place";
        DataBaseName=@"contactsmanager.sqlite";
        
        tabConfearencesAlarm =@"CREATE TABLE IF NOT EXISTS tb_alarm("\
        "_id  INTEGER PRIMARY KEY,"\
        "MemberID INTEGER,"\
        "Message Text," \
        "date VARCHAR(20) );";	
        
        tabConfearencesSchedule = @"CREATE TABLE IF NOT EXISTS tb_schedule("\
        "_id  INTEGER PRIMARY KEY,"\
        "ScheduleId INTEGER,"\
        "ConferenceId INTEGER,"\
        "Message Text,"\
        "Day  VARCHAR(20) ,"\
        "cLanguage  CHAR(1) );";
        
        tabNews=@"CREATE TABLE IF NOT EXISTS tb_news("\
        "_id  INTEGER PRIMARY KEY,"\
        " ConferenceId INTEGER,"\
        " NewsId INTEGER,"\
        " Title VARCHAR(50),"\
        " Message Text,"\
        " Writer VARCHAR(20),"\
        " createtime  VARCHAR(20) ,"\
        " urlImg Text ,"\
        " cLanguage  CHAR(1) );";
        
        tabEleSpeak= @"CREATE TABLE IF NOT EXISTS tb_elespeak("\
        "_id  INTEGER PRIMARY KEY,"\
        " Conference  Text,"\
        " EleSpeakId  INTEGER,"\
        " Title VARCHAR(50),"\
        " Message Text,"\
        " Speaker VARCHAR(20),"\
        " createtime  VARCHAR(20) ,"\
        " headurl  VARCHAR(200) ,"\
        " urlImg Text ,"\
        " cLanguage  CHAR(1) );";
        
        tabFlight =@"CREATE TABLE IF NOT EXISTS tb_flight("\
        " _id  INTEGER PRIMARY KEY,"\
        " FlightId  INTEGER,"\
        "type  CHAR(1),"\
        " Title VARCHAR(200),"\
        " Schedule VARCHAR(200),"\
        " Detail Text,"\
        " Notes Text,"\
        " createtime  VARCHAR(20) ,"\
        " cLanguage  CHAR(1) );";
        
        tabUser =@"CREATE TABLE IF NOT EXISTS tb_user("\
        " _id  INTEGER PRIMARY KEY,"\
        " MemberID INTEGER,"\
        " userName Text,"\
        " cAlarmUpdate Text,"\
        " cNewsUpdate  Text,"\
        " cPickUpUpdate  Text,"\
        " cSeeOffUpdate  Text,"\
        " cPlaceUpdate  Text,"\
        " cEleSpeakUpdate  Text,"\
        " cFristName  Text,"\
        " cLastName  Text,"\
        " cGender  Text,"\
        " cCompanyName  Text,"\
        " cConferenceId INTEGER,"\
        " cMobile Text,"\
        " cPhone Text,"\
        " cBarcode Text,"\
        " pwd Text);";
        
        tabPlace =@"CREATE TABLE IF NOT EXISTS tb_place("\
        " _id  INTEGER PRIMARY KEY,"\
        " PlaceId  INTEGER,"\
        " Title Text,"\
        " Message Text,"\
        " urlPhoto Text,"\
        " cLanguage  CHAR(1),"\
        " CreatedDate Text);";
        
        
        BOOL success;
		NSFileManager *fm=[NSFileManager defaultManager];
		
		NSArray *paths=NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
		NSString *dbPath=([paths count]>0) ? [paths objectAtIndex:0] : nil ;
		NSString *writableDBPath = [dbPath stringByAppendingPathComponent:DataBaseName];
        
		success = [fm fileExistsAtPath:writableDBPath];
		if(!success){
			[fm createFileAtPath:writableDBPath contents:nil attributes:nil];
		}
		self.db = [FMDatabase databaseWithPath:writableDBPath];
		if([self open] == YES){
            
            //                [db beginTransaction];
            //                
            //                NSString *sql=[NSString stringWithFormat:@"drop table if exists %@ ",TAB_CONFERENCESALARM];
            //				[db executeUpdate:sql];
            //                sql=[NSString stringWithFormat:@"drop table if exists %@ ",TAB_CONFERENCESSCHEDULE];
            //				[db executeUpdate:sql];
            //                sql=[NSString stringWithFormat:@"drop table if exists %@ ",TAB_NEWS];
            //				[db executeUpdate:sql];
            //                sql=[NSString stringWithFormat:@"drop table if exists %@ ",TAB_ELESPEAK];
            //				[db executeUpdate:sql];
            //                sql=[NSString stringWithFormat:@"drop table if exists %@ ",TAB_FLIGHT];
            //				[db executeUpdate:sql];
            //                sql=[NSString stringWithFormat:@"drop table if exists %@ ",TAB_USER];
            //				[db executeUpdate:sql];
            //                sql=[NSString stringWithFormat:@"drop table if exists %@ ",TAB_PLACE];
            //				[db executeUpdate:sql];
            //                [db commit];
            
            
            
            [db executeUpdate:tabConfearencesAlarm];
			if ([db hadError]) {
				NSLog(@"Err %d: %@", [db lastErrorCode], [db lastErrorMessage]);
			}
            [db executeUpdate:tabConfearencesSchedule];
			if ([db hadError]) {
				NSLog(@"Err %d: %@", [db lastErrorCode], [db lastErrorMessage]);
			}
            [db executeUpdate:tabNews];
			if ([db hadError]) {
				NSLog(@"Err %d: %@", [db lastErrorCode], [db lastErrorMessage]);
			}
            [db executeUpdate:tabEleSpeak];
			if ([db hadError]) {
				NSLog(@"Err %d: %@", [db lastErrorCode], [db lastErrorMessage]);
			}
            [db executeUpdate:tabFlight];
			if ([db hadError]) {
				NSLog(@"Err %d: %@", [db lastErrorCode], [db lastErrorMessage]);
			}
            [db executeUpdate:tabUser];
			if ([db hadError]) {
				NSLog(@"Err %d: %@", [db lastErrorCode], [db lastErrorMessage]);
			}
            [db executeUpdate:tabPlace];
			if ([db hadError]) {
				NSLog(@"Err %d: %@", [db lastErrorCode], [db lastErrorMessage]);
			}
        }
    }
    return self;
    
}

-(BOOL)beginTransaction{
	return [db beginTransaction];
}
-(BOOL)commit{
	return [db commit];
}

-(BOOL) open{
	if(db!=nil && [db open]==YES){
		[db setShouldCacheStatements:YES];
		return YES;
	}
	else {
		return NO;
	}
	
}
-(void) close{
	if (db!=nil) {
		[db close];
	}
}
-(void)dealloc{
	[db release];
	[super dealloc];
}
//新增通知
-(BOOL)addAlarms:(NSMutableArray *)message{
    for(AlarmData *msg in message){
        [self delAlarmsByDate:msg.dateTime];
        [self addAlarmsSingle:msg];
    }
    
    return true;
}

-(int)addAlarmsSingle:(AlarmData *)alarmData{
    
    Member *member=[(AppDelegate *)[[UIApplication sharedApplication] delegate] userMember];
    
    NSString *sql=[NSString stringWithFormat:@"insert into %@(MemberID,Message,date) values (%d,'%@','%@')",TAB_CONFERENCESALARM,member.memberId,alarmData.message,alarmData.alarmDate];
    [db executeUpdate:sql];
    if ([db hadError]) {
        NSLog(@"Err %d: %@", [db lastErrorCode], [db lastErrorMessage]);
    }
    return 0;
}


/**
 * 根据时间删除通知
 */
-(BOOL)delAlarmsByDate:(NSString *)date{
    NSString *sql=[NSString stringWithFormat:@"delete from %@ where date='%@'",TAB_CONFERENCESALARM,date];
    [db executeUpdate:sql];
    if ([db hadError]) {
		NSLog(@"Err %d: %@", [db lastErrorCode], [db lastErrorMessage]);
	}
    return NO;
}
/**
 * 根据会员号删除通知
 */
-(BOOL)delAlarmsByMemberID{
    NSString *sql=[NSString stringWithFormat:@"delete from %@ where MemberID ='%s'",TAB_CONFERENCESALARM,""];
    [db executeUpdate:sql];
    if ([db hadError]) {
		NSLog(@"Err %d: %@", [db lastErrorCode], [db lastErrorMessage]);
	}
    return NO;
    
}
/**
 * 清空通知
 */
-(BOOL)clearAlarms{
    NSString *sql=[NSString stringWithFormat:@"delete from %@ order by _id",TAB_CONFERENCESALARM];
    [db executeUpdate:sql];
    if ([db hadError]) {
		NSLog(@"Err %d: %@", [db lastErrorCode], [db lastErrorMessage]);
	}
    return NO;
    
}
///**
// * 获取通知
// */
//-(NSMutableArray*)getAlarms{
//     Member *member=[(AppDelegate *)[[UIApplication sharedApplication] delegate]userMember];
//    NSString *sql=[NSString stringWithFormat:@"select Message from %@ where MemberID ='%d' order by _id",TAB_CONFERENCESALARM,member.memberId];
//     FMResultSet *rs=[db executeQuery:sql];
//    if ([db hadError]) {
//		NSLog(@"Err %d: %@", [db lastErrorCode], [db lastErrorMessage]);
//	}
//    NSMutableArray *message=[NSMutableArray arrayWithCapacity:0];
//    NSString *msg=@"";
//    if ([rs next]) {
//        msg=[rs stringForColumn:@"Message"];
//        [message addObject:msg];
//        
//    }
//    [rs close];
//    return message;
//}

/**
 * 新增时刻表
 */
-(BOOL)addSchedule:(NSMutableArray *)scheduleDataList{
    for (ScheduleData  *scheduleData in scheduleDataList) {
        [self addScheduleData:scheduleData];
    }
    return YES;
}
-(int)addScheduleData:(ScheduleData*)scheduleData{
     Member *member=[(AppDelegate *)[[UIApplication sharedApplication] delegate]userMember];
    NSString *sql=[NSString stringWithFormat:@"insert into %@(ScheduleId,ConferenceId,Message,day,cLanguage) values ('%@','%d','%@','%@','%@')",TAB_CONFERENCESSCHEDULE,scheduleData.ID,member.conferenceId,scheduleData.message,scheduleData.date,[Globals getInstance].sLanguage];
    [db executeUpdate:sql];
    if ([db hadError]) {
        NSLog(@"Err %d: %@", [db lastErrorCode], [db lastErrorMessage]);
    }
    return 0;
}
/**
 * 更新会议日程
 *
 */
-(BOOL)updateByScheduleId:(NSMutableArray *)scheduleDataList{
    for (ScheduleData *scheduleData in scheduleDataList) {
        [self updateScheduleData:scheduleData];
    }
    return YES;
}

-(void)updateScheduleData:(ScheduleData *)scheduleData{
     Member *member=[(AppDelegate *)[[UIApplication sharedApplication] delegate]userMember];
    NSString *sql=[NSString stringWithFormat:@"update %@ set ScheduleId= '%@',ConferenceId= '%d',Message= '%@',day='%@',cLanguage='%@' where ScheduleId= '%@'",TAB_CONFERENCESSCHEDULE,scheduleData.ID,member.memberId,scheduleData.message,scheduleData.date,[Globals getInstance].sLanguage,scheduleData.ID];
    [db executeUpdate:sql];
    if ([db hadError]) {
        NSLog(@"Err %d: %@", [db lastErrorCode], [db lastErrorMessage]);
    }
}
/**
 * 删除会议日程
 */
-(BOOL)delScheduleByConfearencesID{
     Member *member=[(AppDelegate *)[[UIApplication sharedApplication] delegate]userMember];
    NSString *sql=[NSString stringWithFormat:@"delete from %@ where ConferenceId='%d' order by _id",TAB_CONFERENCESSCHEDULE,member.conferenceId];
    [db executeUpdate:sql];
    if ([db hadError]) {
		NSLog(@"Err %d: %@", [db lastErrorCode], [db lastErrorMessage]);
	}
    return NO;


}
/**
 * 清空会议日程
 */
-(BOOL)clearSchedule{
    NSString *sql=[NSString stringWithFormat:@"delete from %@ order by _id",TAB_CONFERENCESSCHEDULE];
    [db executeUpdate:sql];
    if ([db hadError]) {
		NSLog(@"Err %d: %@", [db lastErrorCode], [db lastErrorMessage]);
	}
    return NO;
    

}
/**
 * 获取会议日程
 */
-(NSMutableArray *)getScheduleData{
    Member *member=[(AppDelegate *)[[UIApplication sharedApplication] delegate]userMember];
    NSString *sql=[NSString stringWithFormat:@"select * from %@ where MemberID ='%d' and cLanguage='%@' order by _id",TAB_CONFERENCESALARM,member.memberId,[Globals getInstance].sLanguage];
    FMResultSet *rs=[db executeQuery:sql];
    if ([db hadError]) {
		NSLog(@"Err %d: %@", [db lastErrorCode], [db lastErrorMessage]);
	}
    NSMutableArray *scheduleDataList=[NSMutableArray arrayWithCapacity:0];
 
    if ([rs next]) {
        ScheduleData *scheduleData=[[ScheduleData alloc]init];
        scheduleData.ID=[rs stringForColumn:@"ScheduleId"];
        scheduleData.message=[rs stringForColumn:@"Message"];
        scheduleData.date=[rs stringForColumn:@"Day"];
        [scheduleDataList addObject:scheduleData];
        [scheduleData release];
    }
    [rs close];
    return scheduleDataList;

}
-(BOOL)addNews:(NSMutableArray*)newsDataList{
    for (NewsData *newsData in newsDataList) {
        [self delNewsById:newsData.ID];
        [self addNewsData:newsData];
    }
    return YES;
}

-(int)addNewsData:(NewsData *)newsData{
    Member *member=[(AppDelegate *)[[UIApplication sharedApplication] delegate]userMember];
    NSString *sql=[NSString stringWithFormat:@"insert into %@(ConferenceId,NewsId,Title,Message,Writer,createtime,urlImg,cLanguage) values (%d,%@,'%@','%@','%@','%@','%@','%@')",TAB_NEWS,member.conferenceId,newsData.ID,newsData.title,newsData.message,newsData.writer,newsData.dateTime,newsData.arrImg,[Globals getInstance].sLanguage];
    [db executeUpdate:sql];
    if ([db hadError]) {
        NSLog(@"Err %d: %@", [db lastErrorCode], [db lastErrorMessage]);
    }
    return 0;
}

-(BOOL)updateNews:(NSMutableArray*)updateNews{
    for (NewsData *newsData in updateNews) {
        [self updateNewsData:newsData];
    }
    return YES;
}
-(void)updateNewsData:(NewsData *)newsData{
    Member *member=[(AppDelegate *)[[UIApplication sharedApplication] delegate]userMember];
    NSString *sql=[NSString stringWithFormat:@"update %@ set ConferenceId= '%d',NewsId= '%@',Title= '%@',Message='%@',Writer='%@',createtime='%@',cLanguage='%@',urlImg='%@' where NewsId= '%@'",TAB_NEWS,member.conferenceId,newsData.ID,newsData.title,newsData.message,newsData.writer,newsData.dateTime,[Globals getInstance].sLanguage,newsData.arrImg,newsData.ID];
    [db executeUpdate:sql];
    if ([db hadError]) {
        NSLog(@"Err %d: %@", [db lastErrorCode], [db lastErrorMessage]);
    }


}
-(void)delNewsById:(NSString *)newsID{
    NSString *sql=[NSString stringWithFormat:@"delete from %@ where NewsId='%@'",TAB_NEWS,newsID];
    [db executeUpdate:sql];
    if ([db hadError]) {
		NSLog(@"Err %d: %@", [db lastErrorCode], [db lastErrorMessage]);
	}

}
-(BOOL)delNewsByConfearencesID{
     Member *member=[(AppDelegate *)[[UIApplication sharedApplication] delegate]userMember];
    NSString *sql=[NSString stringWithFormat:@"delete from %@ where ConferenceId='%d' order by _id",TAB_NEWS,member.conferenceId];
    [db executeUpdate:sql];
    if ([db hadError]) {
		NSLog(@"Err %d: %@", [db lastErrorCode], [db lastErrorMessage]);
	}
    return NO;

}
-(BOOL)clearNews{
    NSString *sql=[NSString stringWithFormat:@"delete from %@ order by _id",TAB_NEWS];
    [db executeUpdate:sql];
    if ([db hadError]) {
		NSLog(@"Err %d: %@", [db lastErrorCode], [db lastErrorMessage]);
	}
    return NO;

}

/**
 * 获取新闻数据
 */
-(NSMutableArray *)getNewsDatas{
    Member *member=[(AppDelegate *)[[UIApplication sharedApplication] delegate]userMember];
    NSString *sql=[NSString stringWithFormat:@"select * from %@ where ConferenceId=%d and cLanguage='%@' order by _id",TAB_NEWS,member.conferenceId,[Globals getInstance].sLanguage];
    NSLog(@"%@",sql);
    FMResultSet *rs=[db executeQuery:sql];
    if ([db hadError]) {
		NSLog(@"Err %d: %@", [db lastErrorCode], [db lastErrorMessage]);
	}
    NSMutableArray *newsDataList=[NSMutableArray arrayWithCapacity:0];
    
    while ([rs next]) {
        NewsData *newsData=[[NewsData alloc]init];
        newsData.ID=[rs stringForColumn:@"NewsId"];
        newsData.title=[rs stringForColumn:@"Title"];
        newsData.message=[rs stringForColumn:@"Message"];
        newsData.writer=[rs stringForColumn:@"Writer"];
        newsData.dateTime=[rs stringForColumn:@"createtime"];
        newsData.arrImg=[rs stringForColumn:@"urlImg"];
        [newsDataList addObject:newsData];
        [newsData release];
    }
    [rs close];
    return newsDataList;


}
/**
 * 新增附件信息
 */
-(BOOL)addAttachInfo:(NSMutableArray*)attachDataList{
    for ( AttachData *attachData in attachDataList) {
        [self addAttach:attachData];
    }
    return YES;
}
-(void)addAttach:(AttachData *)attachData{
    NSString *sql=[NSString stringWithFormat:@"insert into %@(typeId,type,localImg,urlImg) values ('%d','%@','%@','%@')",TAB_ATTACHINFO,attachData.typeId,attachData.type,attachData.localImg,attachData.urlImg];
    [db executeUpdate:sql];
    if ([db hadError]) {
        NSLog(@"Err %d: %@", [db lastErrorCode], [db lastErrorMessage]);
    }
}
/**
 * 更新附件
 */
-(BOOL)updateAttachInfo:(AttachData *)attachData attachID:(int)attachID{
    NSString *sql=[NSString stringWithFormat:@"update %@ set typeId= '%d',type= '%@',localImg= '%@',urlImg='%@' where _id= '%d'",TAB_ATTACHINFO,attachData.typeId,attachData.type,attachData.localImg,attachData.urlImg,attachID];
    [db executeUpdate:sql];
    if ([db hadError]) {
        NSLog(@"Err %d: %@", [db lastErrorCode], [db lastErrorMessage]);
    }
    return YES;

}
/**
 * 根据附件id删除附件
 */

-(BOOL)delAttachInfoByAttachId:(int)attachID{

    NSString *sql=[NSString stringWithFormat:@"delete from %@ where _id='%d' order by _id",TAB_ATTACHINFO,attachID];
    [db executeUpdate:sql];
    if ([db hadError]) {
		NSLog(@"Err %d: %@", [db lastErrorCode], [db lastErrorMessage]);
	}
    return NO;


}
/**
 * 清空附件
 */
-(BOOL)clearAttach{
    NSString *sql=[NSString stringWithFormat:@"delete from %@ order by _id",TAB_ATTACHINFO];
    [db executeUpdate:sql];
    if ([db hadError]) {
		NSLog(@"Err %d: %@", [db lastErrorCode], [db lastErrorMessage]);
	}
    return NO;

}

/**
 * 根据附件类型，附件id获取附件
 */
-(NSMutableArray*)getAttachData:(int)typeID type:(NSString*)type{
    NSString *sql=[NSString stringWithFormat:@"select * from %@ where type ='%@' and typeId=%d order by _id",TAB_ATTACHINFO,type,typeID];
    FMResultSet *rs=[db executeQuery:sql];
    if ([db hadError]) {
		NSLog(@"Err %d: %@", [db lastErrorCode], [db lastErrorMessage]);
	}
    NSMutableArray *attachDataList=[NSMutableArray arrayWithCapacity:0];
    
    if ([rs next]) {
        AttachData *attachData=[[AttachData alloc]init];
        attachData.Id=[rs intForColumn:@"_id"];
        attachData.localImg=[rs stringForColumn:@"localImg"];
        attachData.urlImg=[rs stringForColumn:@"urlImg"];
        [attachDataList addObject:attachData];
        [attachData release];
    }
    [rs close];
    return attachDataList;


}
/**
 * 新增电子阅读
 */
-(BOOL)addEleSpeak:(NSMutableArray*)eleSpeakDataList{
    for (EleSpeakData *eleSpeakData in eleSpeakDataList) {
        [self delEleSpeakByID:eleSpeakData.ID];
        [self addSpeakData:eleSpeakData];
    }
    return YES;
}
-(int)addSpeakData:(EleSpeakData*)eleSpeakData{
    NSString *sql=[NSString stringWithFormat:@"insert into %@(Conference,EleSpeakId,Title,Message,Speaker,createtime,urlImg,cLanguage) values ('%@',%@,'%@','%@','%@','%@','%@','%@')",TAB_ELESPEAK,eleSpeakData.conference,eleSpeakData.ID,eleSpeakData.title,eleSpeakData.message,eleSpeakData.speak,eleSpeakData.dateTime,eleSpeakData.arrImg,[Globals getInstance].sLanguage];
    [db executeUpdate:sql];
    if ([db hadError]) {
        NSLog(@"Err %d: %@", [db lastErrorCode], [db lastErrorMessage]);
    }

    return 0;
}

/**
 * 更新电子阅读
 */
-(BOOL)updateEleSpeak:(NSMutableArray *)eleSpeakDataList{
    for(EleSpeakData *eleSpeakData in eleSpeakDataList){
    [self updateSpeakData:eleSpeakData];
    }
    return YES;
}

-(void)updateSpeakData:(EleSpeakData *)eleSpeakData{
    NSString *sql=[NSString stringWithFormat:@"update %@ set Conference= '%@',EleSpeakId='%@',Title='%@',Message='%@',Speaker='%@',createtime='%@',urlImg='%@',cLanguage='%@' where EleSpeakId= '%@'",TAB_ELESPEAK,eleSpeakData.conference,eleSpeakData.ID,eleSpeakData.title,eleSpeakData.message,eleSpeakData.speak,eleSpeakData.dateTime,eleSpeakData.arrImg,[Globals getInstance].sLanguage,eleSpeakData.ID];
    [db executeUpdate:sql];
    if ([db hadError]) {
        NSLog(@"Err %d: %@", [db lastErrorCode], [db lastErrorMessage]);
    }
}

/**
 * 删除电子阅读
 */
-(BOOL)delEleSpeakByID:(NSString*)eleSpeakId{

    NSString *sql=[NSString stringWithFormat:@"delete from %@ where EleSpeakId='%@'",TAB_ELESPEAK,eleSpeakId];
    [db executeUpdate:sql];
    if ([db hadError]) {
		NSLog(@"Err %d: %@", [db lastErrorCode], [db lastErrorMessage]);
	}
    return YES;

}
/**
 * 清空电子阅读
 */

-(BOOL)clearEleSpeak{

    NSString *sql=[NSString stringWithFormat:@"delete from %@ order by _id",TAB_ELESPEAK];
    [db executeUpdate:sql];
    if ([db hadError]) {
		NSLog(@"Err %d: %@", [db lastErrorCode], [db lastErrorMessage]);
	}
    return NO;
}



/**
 * 获取电子阅读
 */
-(NSMutableArray *)getEleSpeakData{
    NSString *sql=[NSString stringWithFormat:@"select * from %@ where cLanguage ='%@' order by _id",TAB_ELESPEAK,[Globals getInstance].sLanguage];
    FMResultSet *rs=[db executeQuery:sql];
    
    if ([db hadError]) {
		NSLog(@"Err %d: %@", [db lastErrorCode], [db lastErrorMessage]);
	}
    NSMutableArray *eleSpeakDataList=[NSMutableArray arrayWithCapacity:0];
    
    while([rs next]) {
        EleSpeakData *eleSpeakData=[[EleSpeakData alloc]init];
        eleSpeakData.conference=[rs stringForColumn:@"Conference"];
        eleSpeakData.ID=[rs stringForColumn:@"EleSpeakId"];
        eleSpeakData.title=[rs stringForColumn:@"Title"];
        eleSpeakData.message=[rs stringForColumn:@"Message"];
        eleSpeakData.speak=[rs stringForColumn:@"Speaker"];
        eleSpeakData.dateTime=[rs stringForColumn:@"createtime"];
         eleSpeakData.headUrl=[rs stringForColumn:@"headurl"];
         eleSpeakData.arrImg=[rs stringForColumn:@"urlImg"];
        [eleSpeakDataList addObject:eleSpeakData];
        [eleSpeakData release];
    }
    [rs close];
    return eleSpeakDataList;
    
    
}

-(BOOL)addFlight:(NSMutableArray*)flightDataList{
    for (FlightData *flightData in flightDataList) {
        [self addFlightData:flightData];
    }
    return YES;

}
-(int)addFlightData:(FlightData *)flightData{
    NSString *sql=[NSString stringWithFormat:@"insert into %@(FlightId,type,Schedule,Title,Detail,Notes,createtime,cLanguage) values ('%@','%@','%@','%@','%@','%@','%@','%@')",TAB_FLIGHT,flightData.ID,flightData.type,flightData.schedule,flightData.title,flightData.detail,flightData.notes,flightData.dateTime,[Globals getInstance].sLanguage];
    [db executeUpdate:sql];
    if ([db hadError]) {
        NSLog(@"Err %d: %@", [db lastErrorCode], [db lastErrorMessage]);
    }
    return 0;

}

-(BOOL)updateFlight:(NSMutableArray *)flightDataList{
    for (FlightData *flightData in flightDataList) {
        [self updateFlightData:flightData];
    }
    return YES;
}

-(void)updateFlightData:(FlightData*)flightData{
    NSString *sql=[NSString stringWithFormat:@"update %@ set FlightId= '%@',type='%@',Schedule='%@',Detail='%@',Notes='%@',createtime='%@',cLanguage='%@' where FlightId= '%@' and type='%@'",TAB_FLIGHT,flightData.ID,flightData.type,flightData.schedule,flightData.detail,flightData.notes,flightData.dateTime,[Globals getInstance].sLanguage,flightData.ID,flightData.type];
    [db executeUpdate:sql];
    if ([db hadError]) {
        NSLog(@"Err %d: %@", [db lastErrorCode], [db lastErrorMessage]);
    }
}


-(BOOL)delFlightById:(NSString *)Id type:(NSString *)type{
    NSString *sql=[NSString stringWithFormat:@"delete from %@ where FlightId='%@' and type='%@'",TAB_FLIGHT,Id,type];
    [db executeUpdate:sql];
    if ([db hadError]) {
		NSLog(@"Err %d: %@", [db lastErrorCode], [db lastErrorMessage]);
	}
    return NO;
}
-(BOOL)clearFlight{
    NSString *sql=[NSString stringWithFormat:@"delete from %@ order by _id",TAB_FLIGHT];
    [db executeUpdate:sql];
    if ([db hadError]) {
		NSLog(@"Err %d: %@", [db lastErrorCode], [db lastErrorMessage]);
	}
    return NO;

}
-(NSMutableArray *)getFlight:(NSString*)type{
    NSString *sql=[NSString stringWithFormat:@"select * from %@ where type='%@' and cLanguage ='%@' order by _id",TAB_FLIGHT,type,[Globals getInstance].sLanguage];
    FMResultSet *rs=[db executeQuery:sql];
    if ([db hadError]) {
		NSLog(@"Err %d: %@", [db lastErrorCode], [db lastErrorMessage]);
	}
    NSMutableArray *flightDataList=[NSMutableArray arrayWithCapacity:0];
    
    if ([rs next]) {
        FlightData *flightData=[[FlightData alloc]init];
        flightData.ID=[rs stringForColumn:@"FlightId"];
        flightData.schedule=[rs stringForColumn:@"Schedule"];
        flightData.detail=[rs stringForColumn:@"Detail"];
        flightData.notes=[rs stringForColumn:@"Notes"];
        flightData.dateTime=[rs stringForColumn:@"createtime"];
        flightData.title=[rs stringForColumn:@"Title"];
        [flightDataList addObject:flightData];
        [flightData release];
    }
    [rs close];
    return flightDataList;
}

/**
* 返回通知群组
*/
-(NSMutableArray *)getAlarmsGroup{
    
    NSString *sql=[NSString stringWithFormat:@"select date from %@ group by date",TAB_CONFERENCESALARM];
    FMResultSet *rs=[db executeQuery:sql];
    if ([db hadError]) {
		NSLog(@"Err %d: %@", [db lastErrorCode], [db lastErrorMessage]);
	}
    NSMutableArray *alarmDataList=[NSMutableArray arrayWithCapacity:0];
    
    while([rs next]) {
        NSString *date=[rs stringForColumnIndex:0];
        [alarmDataList addObject:date];
    }
    [rs close];
    return alarmDataList;

}
/**
 * 根据时间返回
 */
-(NSMutableArray *)getAlarmsByDate:(NSString *)sDate{
 NSString *sql=[NSString stringWithFormat:@"select * from %@ where date='%@'",TAB_CONFERENCESALARM,sDate];
    FMResultSet *rs=[db executeQuery:sql];
    if ([db hadError]) {
		NSLog(@"Err %d: %@", [db lastErrorCode], [db lastErrorMessage]);
	}
    NSMutableArray *alarmDataList=[NSMutableArray arrayWithCapacity:0];
    
    if ([rs next]) {
        AlarmData *alarmData=[[AlarmData alloc]init];
        alarmData.dateTime=[rs stringForColumn:@"date"];
        alarmData.message=[rs stringForColumn:@"Message"];
        alarmData.isDate=NO;
        [alarmDataList addObject:alarmData];
        [alarmData release];
    }
    [rs close];
    return alarmDataList;

}

-(NSMutableArray*)getAlarms{
    
    Member *member=[(AppDelegate *)[[UIApplication sharedApplication] delegate] userMember];
    
    NSString *sql=[NSString stringWithFormat:@"select * from %@ where MemberID=%d",TAB_CONFERENCESALARM,member.memberId];
    
    FMResultSet *rs=[db executeQuery:sql];
    if ([db hadError]) {
		NSLog(@"Err %d: %@", [db lastErrorCode], [db lastErrorMessage]);
	}
    NSMutableArray *alarmDataList=[NSMutableArray arrayWithCapacity:0];
    
    while ([rs next]) {
        AlarmData *alarmData=[[AlarmData alloc]init];
        alarmData.dateTime=[rs stringForColumn:@"date"];
        alarmData.message=[rs stringForColumn:@"Message"];
        alarmData.isDate=NO;
        [alarmDataList addObject:alarmData];
        [alarmData release];
    }
    [rs close];
    return alarmDataList;
    
}
-(NSMutableArray*)getPlaceData{
    NSString *sql=[NSString stringWithFormat:@"select * from %@ where cLanguage='%@'",TAB_PLACE,[Globals getInstance].sLanguage];
    FMResultSet *rs=[db executeQuery:sql];
    if ([db hadError]) {
		NSLog(@"Err %d: %@", [db lastErrorCode], [db lastErrorMessage]);
	}
    NSMutableArray *mPlaceDataList=[NSMutableArray arrayWithCapacity:0];
    
    while ([rs next]) {
        PlaceData *placeData=[[PlaceData alloc]init];
        placeData.ID=[rs stringForColumn:@"PlaceId"];
        placeData.title=[rs stringForColumn:@"Title"];
        placeData.message=[rs stringForColumn:@"Message"];
        placeData.urlPhoto=[rs stringForColumn:@"urlPhoto"];
        placeData.dateTime=[rs stringForColumn:@"CreatedDate"];
        [mPlaceDataList addObject:placeData];
        [placeData release];
    }
    [rs close];
    return mPlaceDataList;
}

-(BOOL)addPlace:(NSMutableArray *)placeDataList{
    for (PlaceData *placeData in placeDataList) {
        [self delPlaceData:placeData.ID];
        [self addPlaceData:placeData];
    }
    return YES;
}

-(void)addPlaceData:(PlaceData *)placeData{
    NSString *sql=[NSString stringWithFormat:@"insert into %@(PlaceId,Title,Message,urlPhoto,cLanguage,CreatedDate) values (%@,'%@','%@','%@','%@','%@')",TAB_PLACE,placeData.ID,placeData.title,placeData.message,placeData.urlPhoto,[Globals getInstance].sLanguage,placeData.dateTime];
  //      NSLog(@"%@",sql);
    [db executeUpdate:sql];
    if ([db hadError]) {
        NSLog(@"Err %d: %@", [db lastErrorCode], [db lastErrorMessage]);
    }
}

-(BOOL)delPlaceData:(NSString *)placeID{
    NSString *sql=[NSString stringWithFormat:@"delete from %@ where PlaceId=%@",TAB_PLACE,placeID];
    [db executeUpdate:sql];
    if ([db hadError]) {
		NSLog(@"Err %d: %@", [db lastErrorCode], [db lastErrorMessage]);
	}
    return NO;

}
-(NSMutableArray*)getUserName{
    NSMutableArray *arrayOfData= [NSMutableArray arrayWithCapacity:0];
    
    NSString *sql=[NSString stringWithFormat:@"select * from %@",TAB_USER];
    
    FMResultSet *rs=[db executeQuery:sql];
    
    while ([rs next]) {
        
        Member *member =[[Member alloc] init];
        
        int memberID=[rs intForColumn:@"MemberID"];
        NSString *userName=[rs stringForColumn:@"userName"];
        NSString *pwd=[rs stringForColumn:@"pwd"];
    
        [member setUserName:userName];
        [member setMemberId:memberID];
        [member setPwd:pwd];
        [arrayOfData addObject:member];
        [member release];
    }
    [rs close];

    return  arrayOfData;

}

-(NSInteger)addUser:(Member*)member{
    
    if ([self isExitUser:member])
        return 0;
    
    
    NSString *empty=@"";
    NSString *sql=[NSString stringWithFormat:@"insert into %@(cAlarmUpdate,cNewsUpdate,cPickUpUpdate,cSeeOffUpdate,cPlaceUpdate,cEleSpeakUpdate,cFristName,cLastName,cCompanyName,cConferenceId,cMobile,cPhone,cBarcode,MemberID,userName,pwd) values ('%@','%@','%@','%@','%@','%@','%@','%@','%@','%d','%@','%@','%@','%d','%@','%@')",TAB_USER,empty,empty,empty,empty,empty,empty,member.firstName,member.lastName,member.companyName,member.conferenceId,member.mobile,member.phone,member.barcode,member.memberId,member.userName,member.pwd];
    [db executeUpdate:sql];
    if ([db hadError]) {
        NSLog(@"Err %d: %@", [db lastErrorCode], [db lastErrorMessage]);
    }
    
    return 0;


}

-(BOOL) setUser{
    
    Member *member=[(AppDelegate *)[[UIApplication sharedApplication] delegate] userMember];
    
    NSString *sql=[NSString stringWithFormat:@"select * from %@ where MemberID=%d",tabUser,member.memberId];
    FMResultSet *rs=[db executeQuery:sql];
    while ([rs next]) {
        
        [Globals getInstance].sAlarmUpdate=[rs stringForColumn:@"cAlarmUpdate"
                                            ];
        [Globals getInstance].sAlarmUpdate=[rs stringForColumn:@"cEleSpeakUpdate"
                                            ];
        [Globals getInstance].sAlarmUpdate=[rs stringForColumn:@"cNewsUpdate"
                                            ];
        [Globals getInstance].sAlarmUpdate=[rs stringForColumn:@"cPickUpUpdate"
                                            ];
        [Globals getInstance].sAlarmUpdate=[rs stringForColumn:@"cPlaceUpdate"
                                            ];
        [Globals getInstance].sAlarmUpdate=[rs stringForColumn:@"cSeeOffUpdate"
                                            ];
        break;                      
        
    }
    
    [rs close];

    return YES;



}
-(BOOL)isExitUser:(Member*)member{
    NSString *sql=[NSString stringWithFormat:@"select count(*) as count from %@ where MemberID='%d'",TAB_USER,member.memberId];
    FMResultSet *rs=[db executeQuery:sql];
    if ([db hadError]) {
		NSLog(@"Err %d: %@", [db lastErrorCode], [db lastErrorMessage]);
	}
    int count=0;
    if ([rs next]) {
        count=[rs intForColumn:@"count"];
    }
    [rs close];
    if (count==0)
        return NO;
    else
        return YES;
}

-(Member*)getAutoMember{

    NSString *sql=[NSString stringWithFormat:@"select * from %@ where userName='%@' and pwd='%@'",TAB_USER,
                   [[NSUserDefaults standardUserDefaults] objectForKey:[[Constant getInstance] userName]]
                   ,[[NSUserDefaults standardUserDefaults] objectForKey:[[Constant getInstance] Pwd]]];
    
    FMResultSet *rs=[db executeQuery:sql];
    
    Member *member =[[[Member alloc] init] autorelease];
    
    if ([rs next]) {
        
        int memberID=[rs intForColumn:@"MemberID"];
        NSString *userName=[rs stringForColumn:@"userName"];
        NSString *pwd=[rs stringForColumn:@"pwd"];
        
        NSString *firstName=[rs stringForColumn:@"cFristName"];
        NSString *lastName=[rs stringForColumn:@"cLastName"];
        NSString *companyName=[rs stringForColumn:@"cCompanyName"];
        int conferenceId=[rs intForColumn:@"cConferenceId"];
        NSString *mobile=[rs stringForColumn:@"cMobile"];
        NSString *phone=[rs stringForColumn:@"cPhone"];
        NSString *barcode=[rs stringForColumn:@"cBarcode"];

        [member setUserName:userName];
        [member setMemberId:memberID];
        [member setPwd:pwd];
        [member setBarcode:barcode];
        [member setCompanyName:companyName];
        [member setConferenceId:conferenceId];
        [member setFirstName:firstName];
        [member setLastName:lastName];
        [member setMobile:mobile];
        [member setPhone:phone];
        
    }
    [rs close];
    
    return member;

}

-(BOOL)DelUser:(Member*)member{

    NSString *sql =[NSString stringWithFormat:@"delete from %@ where  MemberID=%d",TAB_USER,member.memberId];
    [db executeUpdate:sql];
    if ([db hadError]) {
		NSLog(@"Err %d: %@", [db lastErrorCode], [db lastErrorMessage]);
	}
    return YES;

}


@end
